create table USER_INFO (
    ID varchar(30 char) not null,
    USER_NAME varchar(200 char),
    IPHONE number,
    PASSWD varchar2(30 char),
    BIRTHDAY date,
    SEX varchar(1 char),
    ADDRESS varchar(200 char),
    UPDATE_DATE date
)tablespace LY_SONIC_DBA;

comment on table USER_INFO is '用户信息表';
comment on column USER_INFO.id is 'key';
comment on column USER_INFO.USER_NAME is '用户名';
comment on column USER_INFO.IPHONE is '手机号';
comment on column USER_INFO.PASSWD is '密码';
comment on column USER_INFO.BIRTHDAY is '出生日期';
comment on column USER_INFO.SEX is '性别';
comment on column USER_INFO.ADDRESS is '地址';
comment on column USER_INFO.UPDATE_DATE is '更新日期';

alter table USER_INFO add primary key (ID);

create index USER_INFO_IND1 on USER_INFO(IPHONE,PASSWD) tablespace LY_SONIC_IND nologging;

create sequence USER_INFO_SEQ
    MINVALUE 1
    MAXVALUE 999999999999999999
    START WITH 1
    INCREMENT BY 1
    CACHE 1000;

create or replace TRIGGER USER_INFO_TIGGER
before insert on USER_INFO
for each row
begin
select USER_INFO_SEQ.nextval into:new.id from sys.dual;
end USER_INFO_TIGGER;
